﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;

namespace DailyNote.AppCode
{
    public class SQLiteDBHelper
    {
        public static readonly string NorthwindConnectionString = "Data Source=Database\\notedata.db3;Pooling=true;FailIfMissing=true";
        private static object lockobj = new object();
        private static SQLiteConnection noteConn = new SQLiteConnection();
        public static bool IsDbExists()
        {
            if (!File.Exists(Utils.MapPath("Database\\notedata.db3")))
            {//数据库文件不存在
                return false;
            }
            return true;
        }
        public static int ExecuteNonQuery(string commandSql)
        {
            if (commandSql.Length == 0) return 0;
            int retval = 0;
            lock (lockobj)
            {
                if (IsDbExists())
                {
                    SQLiteConnection noteConn = new SQLiteConnection();
                    noteConn = new SQLiteConnection(NorthwindConnectionString);
                    noteConn.Open();
                    SQLiteCommand command = noteConn.CreateCommand();
                    command.CommandTimeout = 5000;
                    command.CommandText = commandSql;
                    retval = command.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            return retval;
        }
     
        public static DataSet ExecuteDataset(string commandSql)
        {
            if (commandSql.Length == 0) return null;
            DataSet ds = new DataSet();
            if (IsDbExists())
            {
                SQLiteConnection noteConn = new SQLiteConnection();
                noteConn = new SQLiteConnection(NorthwindConnectionString);
                noteConn.Open();
                SQLiteCommand command = noteConn.CreateCommand();
                command.CommandText = commandSql;
                SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                da.Fill(ds);
                command.Dispose();
                noteConn.Close();
            }
            return ds;
        }
        public static object ExecuteScalar(string commandSql)
        {
            if (commandSql.Length == 0) return 0;
            object retval = 0;
            if (IsDbExists())
            {
                SQLiteConnection noteConn = new SQLiteConnection();
                noteConn = new SQLiteConnection(NorthwindConnectionString);
                noteConn.Open();
                SQLiteCommand command = noteConn.CreateCommand();
                command.CommandText = commandSql;
                retval = command.ExecuteScalar();
                command.Dispose();
                noteConn.Close();
            }
            return retval;
        }
        public static SQLiteDataReader ExecuteReader(string commandSql)
        {
            if (commandSql.Length == 0) return null;
            SQLiteDataReader retval = null;
            if (IsDbExists())
            {
                SQLiteConnection noteConn = new SQLiteConnection();
                noteConn = new SQLiteConnection(NorthwindConnectionString);
                noteConn.Open();
                SQLiteCommand command = noteConn.CreateCommand();
                command.CommandText = commandSql;
                retval = command.ExecuteReader();
            }
            return retval;
        }
    }
}
